package reports;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.io.IOException;
import java.io.FileOutputStream;
import java.util.Map;

/**
 * Created by IntelliJ IDEA.
 * User: raj.chhabra
 * Date: Jan 27, 2011
 * Time: 9:06:13 PM
 * To change this template use File | Settings | File Templates.
 */
public class CustomerTableReport extends ReportUtilities{

    private static final String[] titles = {"Account Open Date", "Customer Code","First Name","Last Name","Business Name", "Address 1","Address 2","City","State","Zip Code"};
    private static final String[] values = {"acct_opn_dt","cust_cd","fname","lname","corp_name","addr1","addr2","city","st_cd","zip_cd"};
    private static final String reportTitle = "Customer Table Report";


    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        CustomerTableReport report = new CustomerTableReport();
        Connection conn = report.getDBConnection(args[0]);
        report.fileName = args[1];

        String query = "select acct_opn_dt,cust_cd, fname, lname, corp_name, addr1, addr2, city, st_cd, zip_cd " +
                        "from cust where acct_opn_dt > ('01-Jul-2009')";

        Statement stmt = conn.createStatement();
        System.out.println("calling executeQuery()");
        ResultSet rs = stmt.executeQuery(query);
        System.out.println("finished executeQuery()");
        try {
            report.createSpreadsheet(rs);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            rs.close();
            stmt.close();
        }
                                        
    }

    public void createSpreadsheet(ResultSet rs) throws SQLException, IOException {
        System.out.println("Starting createSpreadsheet(rs)");
        Workbook wb = new HSSFWorkbook();

        Map<String, CellStyle> styles = createStyles(wb);

        Sheet sheet = createNewSheet((HSSFWorkbook)wb, reportTitle, titles, reportTitle, styles);

        Row row;
        Cell cell;
        int rownum = 2;


        while(rs.next()){
            int j=0;
            row = sheet.createRow(rownum);

            for (String value:values) {
                cell = row.createCell(j);
                if (value.equalsIgnoreCase("cust_cd")){
                    cell.setCellValue(rs.getString(value));
                    cell.setCellStyle(styles.get("cell_normal_centered"));
                }
                else if (value.equalsIgnoreCase("acct_opn_dt")){
                    java.util.Date date = new java.util.Date(rs.getDate(value).getTime());
                    cell.setCellValue(ReportUtilities.formatDate(date));
                    cell.setCellStyle(styles.get("cell_normal_centered"));
                } else {
                    cell.setCellValue(rs.getString(value));
                    cell.setCellStyle(styles.get("cell_normal"));
                }
                    j++;
            }
            rownum++;
        }

        Sheet sheet1 = wb.getSheetAt(0);

        for (int k=0; k<values.length; k++){
            sheet1.autoSizeColumn((short)k);
        }

        // Write the output to a file
        //String file = "CustomerReportCAN.xls";
        String file = fileName;
        if(wb instanceof XSSFWorkbook) file += "x";
        FileOutputStream out = new FileOutputStream(file);
        wb.write(out);
        out.close();
        System.out.println("Finished createSpreadsheet(rs)");

    }

    public void createSpreadsheet() throws SQLException, IOException {
        //To change body of implemented methods use File | Settings | File Templates.
    }
}
